package com.example.xiaomi.mapper;

import com.example.xiaomi.entity.contents;
import com.example.xiaomi.entity.hot;
import com.example.xiaomi.entity.userinfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface HotMapper {
    @Select("SELECT date,sum(repost_num*3 + comment_num*3 + like_num*4) AS hotness\n" +
            "FROM film\n" +
            "WHERE date BETWEEN '${startDate}' AND '${endDate}'\n" +
            "GROUP BY date\n" +
            "ORDER BY date")
    List<hot>findAllhot(@Param("startDate")String startDate, @Param("endDate")String endDate);

    @Select("select date ,count('mid') as count_contents,SUM(repost_num) AS count_repost,sum(comment_num) as count_comment,sum(like_num) as count_like\n" +
            "from film\n" +
            "WHERE date = '${Date}'\n" +
            "GROUP BY date")
    List<contents>findAllcount(String Date);

    @Select("SELECT user_name, contents,topic, (repost_num * 3 + comment_num * 3 + like_num * 4) as hotness\n" +
            "FROM film\n" +
            "WHERE TRIM(topic) <> ''\n" +
            "ORDER BY hotness DESC\n" +
            "LIMIT 20;")
    List<hot>findAllhotcontents();

    @Select("SELECT \n" +
            "    user_name,\n" +
            "    MAX(user_type) AS user_type,\n" +
            "    MAX(official_type) AS official_type,\n" +
            "    MAX(ip) AS ip,\n" +
            "    MAX(followers) AS followers,\n" +
            "    SUM(repost_num*3 + comment_num*3 + like_num*4) AS hotness\n" +
            "FROM film where user_type not like '%null%'\n" +
            "GROUP BY user_name\n" +
            "ORDER BY hotness DESC\n" +
            "LIMIT 10;")
    List<userinfo>findAlltopuser();

    @Select("SELECT user_type ,count(*) as typecount\n" +
            "from (\n" +
            "SELECT user_name,MAX(user_type) AS user_type,SUM(repost_num*3 + comment_num*3 + like_num*4) AS hotness\n" +
            "from film\n" +
            "GROUP BY user_name\n" +
            "ORDER BY hotness DESC\n" +
            "LIMIT 100\n" +
            ") as film1 where user_type not like '%null%'\n" +
            "GROUP BY user_type\n" +
            "ORDER BY typecount DESC")
    List<userinfo>findAlltypecount();

    @Select("SELECT distinct topic,date FROM film WHERE topic LIKE '%${topic}%' ORDER BY date DESC")
    List<hot>findAllkeytopic(String topic);

    @Select("SELECT \n" +
            "    topic,\n" +
            "    user_name,\n" +
            "    date,\n" +
            "    contents,\n" +
            "    hotness\n" +
            "FROM (\n" +
            "    SELECT \n" +
            "        topic,\n" +
            "        user_name,\n" +
            "        date,\n" +
            "        contents,\n" +
            "        SUM(repost_num*3 + comment_num*3 + like_num*4) AS hotness,\n" +
            "        ROW_NUMBER() OVER (PARTITION BY date ORDER BY SUM(repost_num*3 + comment_num*3 + like_num*4) DESC) AS rn\n" +
            "    FROM \n" +
            "        film \n" +
            "    WHERE \n" +
            "        topic LIKE '%${topic}%'\n" +
            "    GROUP BY \n" +
            "        topic,\n" +
            "        user_name,\n" +
            "        date,\n" +
            "        contents\n" +
            "    ORDER BY \n" +
            "        date DESC\n" +
            ") AS ranked\n" +
            "WHERE \n" +
            "    rn = 1\n" +
            "LIMIT 20;;")
    List<hot>findAlltopiccontents(String topic);



}
